#!c:/perl/bin/perl.exe
##########################################################################################################################
##### a script to add object permissions for Users of Wigan-Warriors.com
##########################################################################################################################

##########################################################################################################################
######  TODO list  #######################################################################################################
##########################################################################################################################
##########################################################################################################################

##########################################################################################################################
###### Bug list
##########################################################################################################################
##########################################################################################################################

##########################################################################################################################
# Includes
##########################################################################################################################

use lib qw(../cgi-bin);
use WiganRL;
use LWP::UserAgent;
use HTTP::Request;
use Getopt::Long;

use strict;
no warnings 'uninitialized';
local $SIG{__WARN__} = \&Carp::cluck;  #full call back trace

$|++;  #unbuffered

##########################################################################################################################
# get arguments
##########################################################################################################################

my $season_name= undef;
my $season_startdate= undef;
my $url= undef;
my $squad_name= 'First Team';
my $squad_order= 1;
my $club_type= 'Professional';
my $competition_name= undef;
my $competition_statstype= 'First Team';
my $competition_statsgroupid= 18378;
my $site= 'cherryandwhite';
my $wigan_stadium= 'JJB Stadium';

my $result = GetOptions ("season=s" => \$season_name,
	"seasonStart=s" => \$season_startdate,
	"url=s"   => \$url,
	"competition=s" => \$competition_name,
	"site=s"	=> \$site,
	"squadName=s"  => \$squad_name,
	"squadOrder=i"  => \$squad_order,
	"statsType=s"  => \$competition_statstype,
	"statsGroup=i"  => \$competition_statsgroupid,
	"wiganStadium=s" => \$wigan_stadium,
	);
if (!$result) {
	print "Argument error - exiting\n";
	exit(0);
}

my %MONTH_TO_NUM= (
	'Jan' => 1,
	'Feb' => 2,
	'Mar' => 3,
	'Apr' => 4,
	'May' => 5,
	'Jun' => 6,
	'Jul' => 7,
	'Aug' => 8,
	'Sep' => 9,
	'Oct' => 10,
	'Nov' => 11,
	'Dec' => 12,
);

##########################################################################################################################
# site setup and location variable declarations
##########################################################################################################################

my ($SITE_BASE_URL,$SITE_BASE_DIR,$CGI_BASE_URL,$CGI_BASE_DIR,$EMAIL_BASE_DIR,$MAIL_SMTP_SERVER);
my ($SEND_EMAILS,$ADMIN_NAME,$ADMIN_EMAIL,$MAIL_SCRIPT,$SITE_DATA_DIR);
my ($DATABASE_NAME,$DATABASE_SERVER,$DATABASE_USERNAME,$DATABASE_PASSWORD,$DATABASE_TYPE);

load_config('../cgi-bin/wiganrl.ini');

my $USER_UPLOADDIR= "$SITE_DATA_DIR/users";
my $USER_UPLOADURL= "data/users";
my $SEARCH_DATE_DAYS= 3;

#control main club of site with variable;
my $CLUB_ID= 10043;

##########################################################################################################################
# class and database setup
##########################################################################################################################

my $cgi= CGI->new();
$cgi->param('CGI_BASE_URL',$CGI_BASE_URL);
#print "Content-Type: text/html\n\n";
#print "Status: ".$cgi->param('status');

my $dbh= Diva::DB->new();
$dbh->set( {
	DATABASE_NAME       => $DATABASE_NAME,
	DATABASE_SERVER     => $DATABASE_SERVER,
	DATABASE_USERNAME   => $DATABASE_USERNAME,
	DATABASE_PASSWORD   => $DATABASE_PASSWORD,
	DATABASE_TYPE       => $DATABASE_TYPE,
	} );
$dbh->connect();
$dbh->debug(0);

#use Devel::Profile;
#use DBIx::Profile;
#$dbh->handle->setLogFile('c:/wwcom/v2.0/cgi-bin/db.log');

#ordering of these lines is important!
WiganRL::DataObject->dbh($dbh);
WiganRL::DataObject->cgi($cgi);

Diva::DataObject->init_contenttypes('WWCOM');  #loads contenttypes

WiganRL::DataObject->init_class();
WiganRL::Search->init_class();

WiganRL::Search->dbh($dbh);

#initialise $cgi
$cgi->param('UserID',12032);


#make request
my $agent= new LWP::UserAgent;
$agent->agent("Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0" . $agent->agent);
print "requesting";
$agent->timeout(5);  #30 second timeout
my $res= $agent->get($url);
my $content;
if ($res->is_success) {
	print "Sucess!\n";
	$content= $res->content;
} else {
	print "Fail";
	exit(0);
}

#load wigan
my $wigan= WiganRL::DataObject::Club->new($CLUB_ID);

#parse for matches and fixtures
if (!defined $season_name) {
	print "No season name defined\n";
	exit(0);
}
#guess season start date
if (!defined $season_startdate) {
	if ($season_name=~ /^(\d{4})/) {
		$season_startdate= "$1-09-00";
	}
	if (!defined $season_startdate) {
		print "Couldn't find season start date\n";
		exit(0);
	}
}
#update database - season
my $season= insert_season($season_name,$season_startdate);


#find competition name
if (!defined $competition_name) {
	$content=~ m~<u><b>\d+\-\d+\s+([^\<]+)</b></u>~iso;
	$competition_name= $1;
	if (!defined $competition_name) {
		print "Couldn't find competition name\n";
		exit(0);
	} else {
		print "Major comeptition name: $competition_name\n";
	}
}
my $competition_obj= insert_competition($competition_name,$season,$competition_statstype,$competition_statsgroupid);
$competition_name= $competition_obj->data('COMPETITION_NAME');
my $round= 0;

#parse for matches
my $pattern;
my $patterncode;
my $leaguepattern;
my $leaguepatterncode;
if ($site eq 'cherryandwhite') {
	$pattern= q~<tr>\s+
              <td[^>]+>(\d{2})/(\d{2})/(\d{4})</td>\s+
              <td[^>]+>\s*(?:<font\s+ color="\#FF0000">([\w\s]+)\:?</font>\:?)?\s*([\w\s]+)\s*</td>\s+
              <td[^>]+">\s*(\w{4})\s*</td>\s+
              <td[^>]+>[\w\s]+ \s (\d+)-(\d+)</td>~;
	$patterncode= 	<<__END_CODE__;
		\$day= \$1;
		\$month= \$2;
		\$year= \$3;
		\$competition= \$4;
		\$opposition= \$5;
		\$location= \$6;
		\$stadium= '';
		\$wigan_score= \$7;
		\$opp_score= \$8;
		\$hour= '';
		\$min= '';
__END_CODE__
	$leaguepattern= q~\d+\s+ (?:<b>)?([^\d\<]+)(?:</b>)?\s+ ?(\d+)\s+ (\d+)\s+  (\d+)\s+  (\d+)\s+  \-\s+  (\d+)\s+  (\d+)\s+  (\d+)\s+~;
	$leaguepatterncode= <<__END_CODE__;
		\$club= \$1;
		\$played= \$2;
		\$won= \$3;
		\$drawn= \$4;
		\$lost= \$5;
		\$for= \$6;
		\$against= \$7;
		\$diff= '';
		\$points= \$8;
__END_CODE__
} elsif ($site eq 'rfl') {
	$pattern= q~<tr>\s+
        <td\s+ class="tabletdgrey"><p>\w{3}\s+ (\d+)\s+ (\w{3})</p></td>\s+
        <td\s+ class="tabletdwhite"><p><strong>([^<]+)</strong><span>\s+ V \s+</span><strong>([^<]+)</strong><br\s+ />\s+
          ko:\s+ ([^\,\.]+)(?:\.(\d+))?\,\s+ Venue\:\s+ \b([^<]+)</p></td>~;

	$patterncode= 	<<__END_CODE__;
		\$day= \$1;
		\$month= \$MONTH_TO_NUM{\$2};
		\$year= "2007";
		\$competition= undef;
		my \$team1= \$3; my \$team2= \$4;
		if (\$5 ne 'tbc') {
			\$hour= \$5;
			\$min= \$6;
		} else {
			\$hour= '';
			\$min= '';
		}
		\$stadium= \$7;
		\$wigan_score= '';
		\$opp_score= '';
		\$opposition= ((\$team1=~ /Wigan/) ? \$team2 : \$team1);
		\$location= ((\$team1=~ /Wigan/) ? 'Home' : 'Away');
		#if (\$3!~ /Wigan/ && \$4!~ /Wigan/) {
	#		next;
	#	}
__END_CODE__

}


my $continue= promptUser('Scan Matches','yes');
if ($continue ne 'yes') {
	goto TABLE;
}

#print "$patterncode";
my $wigan_stadium_obj= 	undef;
insert_stadium($wigan_stadium) if ($wigan_stadium ne '');

my ($day,$month,$year,$competition,$opposition,
	$location,$wigan_score,$opp_score,$stadium,$hour,$min);
while ($content=~ m~$pattern~isgx) {
	eval($patterncode);
	
	print "Found match\n=========\n";
	if (!defined $competition) {
		$competition= $competition_name;
		$round++;
	}
	my $kotime;
	if ($hour ne '') {
		$kotime= sprintf("%04d-%02d-%02d %02d:%02d:%02d",$year,$month,$day,$hour,$min,0);
	} else {
		$kotime= sprintf("%04d-%02d-%02d",$year,$month,$day);
	}
	my $match_name= "Round $round";
	print "Competition: $competition\n";
	print "Opposition: $opposition\n";
	print "Location: $location\n";
	print "Stadium: $stadium\n";
	print "Match name: $match_name\n";
	print "KO time: $kotime\n";
	print "Result: $wigan_score-$opp_score\n";

	my $continue= promptUser('Accept details (yes|no|skip)','yes');
	if ($continue eq 'skip') {
		next;
	}
	if ($continue ne 'yes') {
		$competition= promptUser('Competition',$competition);
		$match_name= promptUser('Match name',$match_name);
		$kotime= promptUser('KO time',$kotime);		
		$opposition= promptUser('Opposition',$opposition);
		$location= promptUser('Location (Home/Away)',$location);
		$stadium= promptUser('Stadium',$stadium);
		$wigan_score= promptUser('Wigan score',"$wigan_score");		
		$opp_score= promptUser('Opposition score',"$opp_score");
	}
	my $match;


	$match->{'competition'}= insert_competition($competition,$season,$competition_statstype,$competition_statsgroupid);
	$match->{'opposition'}= insert_club($opposition);

	$match->{'wigansquad'}= insert_squad($wigan,$season,$squad_name,$squad_order,$wigan_stadium_obj);

	if ($stadium ne '' && $stadium ne 'tbc' && $location ne 'Home') {
		$match->{'stadium'}= insert_stadium($stadium);
	} else {
		$match->{'stadium'}= undef;
	}

	if ($location=~ /^H/i) {  #its an a home  match - no stadium specified
		$location= 'Home';
	} elsif ($location=~ /^A/i) {  #its an a away match - no stadium specified
		$location= 'Away';
	} elsif ($location eq '') {
		#its an away match with a specified stadium
		if (defined $match->{'stadium'} && 
			$match->{'stadium'}->id != $match->{'wigansquad'}->stadium->id) {
			$location= 'Away';
		} else {
			$location= 'Home';			
		}
	}

	#add opposition squad
	if ($location eq 'Away') {
		$match->{'oppsquad'}= insert_squad($match->{'opposition'},
			$season,$squad_name,$squad_order,$match->{'stadium'});
	} else {
		$match->{'oppsquad'}= insert_squad($match->{'opposition'},
			$season,$squad_name,$squad_order,undef);
	}

	my $home= $wigan;
	my $away= $match->{'opposition'};
	my $homesquad= $match->{'wigansquad'};
	my $awaysquad= $match->{'oppsquad'};
	my $homescore= $wigan_score;
	my $awayscore= $opp_score;
	if ($location=~ /^A/i) {
		$away= $wigan;
		$home= $match->{'opposition'};
		$awaysquad= $match->{'wigansquad'};
		$homesquad= $match->{'oppsquad'};
		$awayscore= $wigan_score;
		$homescore= $opp_score;
	}	

	#update match
	insert_match($match->{'competition'},$match_name,$kotime,$home,$away,
		$homescore,$awayscore,$homesquad,$awaysquad,$match->{'stadium'});

	#exit(0);
}

TABLE: 	$continue= promptUser('Scan Table','yes');

if ($continue ne 'yes') {
	exit(0);
}

my $table= insert_table($competition_obj);
my $dltable= insert_dltable($table);

my ($club,$played,$won,$drawn,$lost,$for,$against,$diff,$points);
while ($content=~ m~$leaguepattern~isgx) {
	eval($leaguepatterncode);
	
	print "Found tablerow\n=========\n";
	print "Club: $club\n";
	print "Row: $played $won $drawn $lost $for $against $points\n";
	my $continue= promptUser('Accept details (yes|no|skip)','yes');
	if ($continue eq 'skip') {
		next;
	}
	if ($continue ne 'yes') {
		$club= promptUser('Club',$club);
	}
	my $club_obj= insert_club($club);
	insert_ltablerow($dltable,$club_obj,$played,$won,$drawn,$lost,$for,$against,$points);
}




sub insert_season {
	my $season_name= shift;
	my $season_startdate= shift;

	my $s= WiganRL::Search->new;  $s->clear_cache_type('SEASON');
	$s->table('SEASON');
	$s->where("SEASON_NAME='$season_name'");
	my $res= $s->search;
	my $season;
	if ($#$res<0) {
		#insert new season
		print "New Season\n===============\n";
		print "Name: $season_name\n";
		print "Start date: $season_startdate\n";
		my $continue= promptUser(
			'Accept details','yes');
		if ($continue ne 'yes') {
			$season_name= promptUser('Name',$season_name);
			$season_startdate= promptUser('Start date',$season_startdate);
		}
		$season= WiganRL::DataObject::Season->new;
		$cgi->param('SEASON_NAME',$season_name);
		$cgi->param('SEASON_STARTDATE',$season_startdate);
		$cgi->param('SEASON_ACTIVE','False');
		$season->update;
		print "*** Inserted season: ".$season->id."\n";
	} else {
		$season= $res->[0];
		print "Found season: ".$season->id."\n";
	}
	return $season;
}

sub insert_competition {
	my $competition_name= shift;
	my $season= shift;
	my $competition_statstype= shift;
	my $competition_statsgroupid= shift;

	my $s= WiganRL::Search->new; $s->clear_cache_type('COMPETITION');
	$s->table('COMPETITION');
	$s->where("COMPETITION_NAME='$competition_name' and ".
		"FK_PARENT_ID='".$season->id."'");
	my $res= $s->search;
	my $competition;
	if ($#$res<0) {
		#insert new competition
		print "New Competition\n===============\n";
		print "Name: $competition_name\n";
		print "Stats type: $competition_statstype\n";
		print "Stats group ID: $competition_statsgroupid\n";
		my $continue= promptUser(
			'Accept details','yes');
		if ($continue ne 'yes') {
			$competition_name= promptUser('Name',$competition_name);
			$competition_statstype= promptUser('Stats type',$competition_statstype);
			$competition_statsgroupid= promptUser('Stats group ID',$competition_statsgroupid);
		}
		$competition= WiganRL::DataObject::Competition->new;
		$cgi->param('ParentID',$season->id);
		$cgi->param('COMPETITION_NAME',$competition_name);
		$cgi->param('COMPETITION_STATSTYPE',$competition_statstype);
		$cgi->param('FK_STATSGROUP_ID',$competition_statsgroupid);
		$cgi->param('COMPETITION_ICON','');
		$competition->update;
		print "*** Inserted competition: ".$competition->id."\n";
	} else {
		$competition= $res->[0];
		print "Found competition: ".$competition->id."\n";
	}
	return $competition;
}


sub insert_club {
	my $club_name= shift;

	my $club_nickname;
	my $club_sname= '';
	my $club_type= 'Professional';
	#insert new season
	print "New Club\n===============\n";
	print "Name: $club_name\n";
	print "Nickname: $club_nickname\n";
	print "Short name: $club_sname\n";
	print "Type: $club_type\n";
	my $continue= promptUser(
		'Accept details','yes');
	if ($continue ne 'yes') {
		$club_name= promptUser('Name',$club_name);
		$club_nickname= promptUser('Nickname',$club_nickname);
		$club_sname= promptUser('Short name',$club_sname);
		$club_type= promptUser('Type',$club_type);
	}

	$club_name=~ /^(\w+)/;
	my $s= WiganRL::Search->new;  $s->clear_cache_type('CLUB');
	$s->table('CLUB');
	$s->where("CLUB_NAME like '%$1%'");
	my $res= $s->search;
	my $club;
	if ($#$res>=0) {
		print "Select Club:\n";
		my $i= 1;
		for (@$res) {
			print "[$i] ".$_->id.": ".$_->data('CLUB_NAME')." ".$_->data('CLUB_NICKNAME')."\n";
			$i++;
		}
		print "[0] *** New club ***\n";
		my $id= promptUser('Select club',1);
		if ($id) {
			$club= $res->[$id-1];
			print "Found club: ".$club->id."\n";
			return $club;
		}
	}

	if (!defined $club) {
		$club= WiganRL::DataObject::Club->new;
		print "*** Inserted club: ".$club->id."\n";
		$cgi->param('CLUB_NAME',$club_name);
		$cgi->param('CLUB_NICKNAME',$club_nickname);
		$cgi->param('CLUB_SNAME',$club_sname);
		$cgi->param('CLUB_TYPE',$club_type);
		$club->update;
	}
	
	return $club;
}


sub insert_stadium {
	my $stadium_name= shift;

	#insert new season
	print "New Stadium\n===============\n";
	print "Name: $stadium_name\n";
	my $continue= promptUser(
		'Accept details','yes');
	if ($continue ne 'yes') {
		$stadium_name= promptUser('Name',$stadium_name);
	}

	$stadium_name=~ /^(\w+)/;
	my $s= WiganRL::Search->new;  $s->clear_cache_type('STADIUM');
	$s->table('STADIUM');
	$s->where("STADIUM_NAME like '%$1%'");
	my $res= $s->search;
	my $stadium;
	if ($#$res>=0) {
		print "Select Stadium:\n";
		my $i= 1;
		for (@$res) {
			print "[$i] ".$_->id.": ".$_->data('STADIUM_NAME')."\n";
			$i++;
		}
		print "[0] *** New stadium ***\n";
		print "[-1] Skip stadium\n";
		my $id= promptUser('Select stadium',1);
		if ($id<0) {
			return undef;
		}
		if ($id) {
			$stadium= $res->[$id-1];
			print "Found stadium: ".$stadium->id."\n";
			return $stadium;
		}
	}

	if (!defined $stadium) {
		$stadium= WiganRL::DataObject::Stadium->new;
		$cgi->param('STADIUM_NAME',$stadium_name);
		$stadium->update;
		print "*** Inserted stadium: ".$stadium->id."\n";
	}
	
	return $stadium;
}



sub insert_squad {
	my $club= shift;
	my $season= shift;
	my $squad_name= shift;
	my $squad_order= shift;
	my $stadium= shift;

	my $s= WiganRL::Search->new;  $s->clear_cache_type('SQUAD');
	$s->table('SQUAD');
	$s->where("FK_CLUB_ID='".$club->id."' and FK_SEASON_ID='".$season->id."'");
	my $res= $s->search;
	my $squad;
	if ($#$res<0) {
		#insert new squad
		print "New ".$club->data('CLUB_NAME')." Squad\n===============\n";
		print "Name: $squad_name\n";
		print "Order: $squad_order\n";
		my $continue= promptUser(
			'Accept details','yes');
		if ($continue ne 'yes') {
			$squad_name= promptUser('Name',$squad_name);
			$squad_order= promptUser('Order',$squad_order);
		}
		$squad= WiganRL::DataObject::Squad->new;
		$cgi->param('SQUAD_NAME',$squad_name);
		$cgi->param('SQUAD_ORDER',$squad_order);
		$cgi->param('FK_CLUB_ID',$club->id);
		$cgi->param('FK_SEASON_ID',$season->id);
		$cgi->param('FK_STADIUM_ID','');
		$squad->update;
		print "*** Inserted squad: ".$squad->id."\n";
	} else {
		if ($#$res>0) {
			print "Select Squad:\n";
			my $i= 1;
			for (@$res) {
				print "[$i] ".$_->id.": ".$club->data('CLUB_NAME')." ".$_->data('SQUAD_NAME')."\n";
				$i++;
			}
			my $id= promptUser('Select squad',1);			
			$squad= $res->[$id-1];
		} else {
			$squad= $res->[0];
		}
		print "Found squad: ".$squad->id."\n";
	}
	return $squad;
}


sub insert_match {
	my $competition= shift;
	my $match_name= shift;
	my $match_kotime= shift;
	my $home= shift;
	my $away= shift;
	my $match_homescore= shift;
	my $match_awayscore= shift;
	my $homesquad= shift;
	my $awaysquad= shift;
	my $location= shift;

	my $s= WiganRL::Search->new; $s->clear_cache_type('MATCHSTATS');
	$s->table('MATCHSTATS');
	$s->where("FK_PARENT_ID='".$competition->id.
		"' and FK_HOME_ID='".$home->id."' and FK_AWAY_ID='".$away->id."'");
	my $res= $s->search;
	my $match= undef;

	if ($#$res>=0) {
		print "Select Match:\n";
		my $i= 1;
		for (@$res) {
			print "[$i] ".$_->id.": ".$_->data('MATCH_NAME').": ".$_->get_field('MATCHSTATS_RESULT')."\n";
			$i++;
		}
		print "[0] *** New match ***\n";
		print "[-1] --- Skip Match ---\n";
		my $id= promptUser('Select match',-1);
		if ($id<0) {
			print "Skipping";
			return undef;
		} elsif ($id) {
			$match= $res->[$id-1];
			print "Found match: ".$match->id."\n";
		}
	}

	#insert new match
	if (!defined $match) {
		$match= WiganRL::DataObject::Matchstats->new;
		print "New Match\n===============\n";
	} else {
		print "Update Match ".$match->id."\n===============\n";
	}
	print "Name: $match_name\n";
	print "KO time: $match_kotime\n";
	print "Home score: $match_homescore\n";
	print "Away score: $match_awayscore\n";
	my $continue= promptUser(
		'Accept details','yes');
	if ($continue ne 'yes') {
		$match_name= promptUser('Name',$match_name);
		$match_kotime= promptUser('KO time',$match_kotime);
		$match_homescore= promptUser('Home score',$match_homescore);
		$match_awayscore= promptUser('Away score',$match_awayscore);
	}
	
	$cgi->param('MATCH_NAME',$match_name);
	$cgi->param('MATCH_KOTIME_DATE',$match_kotime);
	$cgi->param('FK_HOME_ID',$home->id);
	$cgi->param('FK_AWAY_ID',$away->id);
	$cgi->param('MATCH_VENUE','__DEFAULT__');
	$cgi->param('MATCH_HOMESCORE',$match_homescore);
	$cgi->param('MATCH_AWAYSCORE',$match_awayscore);
	$cgi->param('FK_HOMESQUAD_ID',$homesquad->id);
	$cgi->param('FK_AWAYSQUAD_ID',$awaysquad->id);
	$cgi->param('MATCHSTATS_MOTMACTIVE','False');
	$cgi->param('MATCHSTATS_STATSACTIVE','True');
	if (defined $location) {
		$cgi->param('FK_STADIUM_ID',$location->id);
	} else {
		$cgi->delete('FK_STADIUM_ID');
	}
	$cgi->param('ParentID',$competition->id);
	$match->update;
	print "*** Inserted match: ".$match->id."\n";

	return $match;
}

sub insert_table {
	my $competition= shift;
	my $table_name= 'Table';
	my $table_description= '';

	#insert new table
	print "New League Table\n===============\n";
	print "Name: $table_name\n";
	print "Description: $table_description\n";
	my $continue= promptUser(
		'Accept details','yes');
	if ($continue ne 'yes') {
		$table_name= promptUser('Name',$table_name);
		$table_description= promptUser('Description',$table_description);
	}

	my $s= WiganRL::Search->new;  $s->clear_cache_type('LTABLE');
	$s->table('LTABLE');
	$s->where("FK_COMPETITION_ID='".$competition->id."'");
	my $res= $s->search;
	my $table;
	if ($#$res>=0) {
		print "Select Table:\n";
		my $i= 1;
		for (@$res) {
			print "[$i] ".$_->id.": ".$_->get_field('COMPETITION_COMPETITION_NAME')." ".$_->data('LTABLE_NAME')."\n";
			$i++;
		}
		print "[0] *** New table ***\n";
		my $id= promptUser('Select table',1);
		if ($id) {
			$table= $res->[$id-1];
			print "Found table: ".$table->id."\n";
			return $table;
		}
	}

	if (!defined $table) {
		$table= WiganRL::DataObject::Ltable->new;
		$cgi->param('LTABLE_NAME',$table_name);
		$cgi->param('LTABLE_DESCRIPTION',$table_description);
		$cgi->param('FK_COMPETITION_ID',$competition->id);
		$table->update;
		print "*** Inserted table: ".$table->id."\n";
	}
	
	return $table;
}


sub insert_dltable {
	my $table= shift;
	my $table_date= '';

	#insert new table
	print "New Dated League Table\n===============\n";
	print "Date: $table_date\n";
	my $continue= promptUser(
		'Accept details','yes');
	if ($continue ne 'yes') {
		$table_date= promptUser('Date',$table_date);
	}

	my $s= WiganRL::Search->new;  $s->clear_cache_type('DLTABLE');
	$s->table('DLTABLE');
	$s->where("FK_LTABLE_ID='".$table->id."'");
	my $res= $s->search;
	my $dltable;
	if ($#$res>=0) {
		print "Select Dated Table:\n";
		my $i= 1;
		for (@$res) {
			print "[$i] ".$_->id.": ".$_->data('DLTABLE_DATE')."\n";
			$i++;
		}
		print "[0] *** New dated table ***\n";
		my $id= promptUser('Select dated table',1);
		if ($id) {
			$dltable= $res->[$id-1];
			print "Found dated table: ".$dltable->id."\n";
			return $dltable;
		}
	}

	if (!defined $dltable) {
		$dltable= WiganRL::DataObject::Dltable->new;
		$cgi->param('DLTABLE_DATE',$table_date);
		$cgi->param('FK_LTABLE_ID',$table->id);
		$dltable->update;
		print "*** Inserted dated table: ".$dltable->id."\n";
	}
	
	return $dltable;
}


sub insert_ltablerow {
	my $dltable= shift;
	my $club= shift;
	my $played= shift;
	my $won= shift;
	my $drawn= shift;
	my $lost= shift;
	my $for= shift;
	my $against= shift;
	my $points= shift;
	my $notes= shift;
	my $highlight= shift;

	#insert new table row
	print "New Dated Table Row \n===============\n";
	print "Club: ".$club->data('CLUB_NAME')."\n";
	print "Played: $played\n";
	print "Won: $won\n";
	print "Drawn: $drawn\n";
	print "Lost: $lost\n";
	print "For: $for\n";
	print "Against: $against\n";
	print "Points: $points\n";
	print "Notes: $notes\n";
	print "Highlight: $highlight\n";
	my $continue= promptUser(
		'Accept details','yes');
	if ($continue ne 'yes') {
		$played= promptUser('Played',$played);
		$won= promptUser('Won',$won);
		$drawn= promptUser('Drawn',$drawn);
		$lost= promptUser('Lost',$lost);
		$for= promptUser('For',$for);
		$against= promptUser('Against',$against);
		$points= promptUser('Points',$points);
		$notes= promptUser('Notes',$notes);
		$highlight= promptUser('Highlight',$highlight);
	}

	my $s= WiganRL::Search->new;  $s->clear_cache_type('LTABLEROW');
	$s->table('LTABLEROW');
	$s->where("FK_DLTABLE_ID='".$dltable->id."' and FK_CLUB_ID='".$club->id."'");
	my $res= $s->search;
	my $ltablerow;
	if ($#$res>=0) {
		print "Select Table Row:\n";
		my $i= 1;
		for (@$res) {
			print "[$i] ".$_->id.": ".$_->get_field('CLUB_CLUB_NAME')." ".$_->data('LTABLEROW_PLAYED')." ".$_->data('LTABLEROW_POINTS')."\n";
			$i++;
		}
		print "[0] *** New table row ***\n";
		my $id= promptUser('Select table row',1);
		if ($id) {
			$ltablerow= $res->[$id-1];
			print "Found table row: ".$ltablerow->id."\n";
			return $ltablerow;
		}
	}

	if (!defined $ltablerow) {
		$ltablerow= WiganRL::DataObject::Ltablerow->new;
		$cgi->param('LTABLE_PLAYED',$played);
		$cgi->param('LTABLE_WON',$won);
		$cgi->param('LTABLE_DRAWN',$drawn);
		$cgi->param('LTABLE_LOST',$lost);
		$cgi->param('LTABLE_FOR',$for);
		$cgi->param('LTABLE_AGAINST',$against);
		$cgi->param('LTABLE_POINTS',$points);
		$cgi->param('LTABLE_NOTES',$notes);
		$cgi->param('LTABLE_HIGHLIGHT',$highlight);
		$cgi->param('FK_DLTABLE_ID',$dltable->id);
		$cgi->param('FK_CLUB_ID',$club->id);
		$ltablerow->update;
		print "*** Inserted table row: ".$ltablerow->id."\n";
	}
	
	return $ltablerow;
}


sub load_config {
	my $inifile= shift;
	
  ### get path for ini file
  my($pack,$file,$line,$sub) = caller;
  my($dev,$dirs,$id) = File::Spec->splitpath($file);
  my $IniFile = $dev . $dirs . $inifile;

  ### check file exists
  if (!(-e $IniFile)) {
	  die "$IniFile does not exist";
  }
  
  ### get contents of ini file
  use Config::IniFiles;
  my $cfg = new Config::IniFiles( -file => $IniFile ) 
  || die "Could not read $IniFile\n";

  ### Paths
	$SITE_BASE_URL= $cfg->val('paths', 'SITE_BASE_URL');
	$SITE_BASE_DIR= $cfg->val('paths', 'SITE_BASE_DIR');
	$SITE_DATA_DIR= $cfg->val('paths', 'SITE_DATA_DIR');
	$CGI_BASE_URL= $cfg->val('paths', 'CGI_BASE_URL');
	$CGI_BASE_DIR= $cfg->val('paths', 'CGI_BASE_DIR');
	
  ### emails
	$SEND_EMAILS= $cfg->val('emails', 'SEND_EMAILS');
	$EMAIL_BASE_DIR= $cfg->val('emails', 'EMAIL_BASE_DIR');
	$MAIL_SMTP_SERVER= $cfg->val('emails', 'MAIL_SMTP_SERVER');
	$ADMIN_NAME= $cfg->val('emails', 'ADMIN_NAME');
	$ADMIN_EMAIL= $cfg->val('emails', 'ADMIN_EMAIL');
	$MAIL_SCRIPT= $cfg->val('emails', 'MAIL_SCRIPT');

	### database
	$DATABASE_NAME= $cfg->val('database', 'DATABASE_NAME');
	$DATABASE_SERVER= $cfg->val('database', 'DATABASE_SERVER');
	$DATABASE_USERNAME= $cfg->val('database', 'DATABASE_USERNAME');
	$DATABASE_PASSWORD= $cfg->val('database', 'DATABASE_PASSWORD');
	$DATABASE_TYPE= $cfg->val('database', 'DATABASE_TYPE');
	return;
}



#----------------------------(  promptUser  )-----------------------------#
#                                                                         #
#  FUNCTION:	promptUser                                                #
#                                                                         #
#  PURPOSE:	Prompt the user for some type of input, and return the    #
#		input back to the calling program.                        #
#                                                                         #
#  ARGS:	$promptString - what you want to prompt the user with     #
#		$defaultValue - (optional) a default value for the prompt #
#                                                                         #
#-------------------------------------------------------------------------#

sub promptUser {

   #-------------------------------------------------------------------#
   #  two possible input arguments - $promptString, and $defaultValue  #
   #  make the input arguments local variables.                        #
   #-------------------------------------------------------------------#

   my ($promptString,$defaultValue) = @_;

   #-------------------------------------------------------------------#
   #  if there is a default value, use the first print statement; if   #
   #  no default is provided, print the second string.                 #
   #-------------------------------------------------------------------#

   if ($defaultValue) {
      print $promptString, " [", $defaultValue, "]: ";
   } else {
      print $promptString, ": ";
   }

   $| = 1;               # force a flush after our print   
   my $input = <STDIN>;         # get the input from STDIN (presumably the keyboard)


   #------------------------------------------------------------------#
   # remove the carige return and newline characters from the end of the input the user  #
   # gave us.                                                         #
   #------------------------------------------------------------------#

	if ($^O eq 'MSWin32') {
	   chop $input;
	   chop $input;
	} else {
		chomp $input;
	}

   #-----------------------------------------------------------------#
   #  if we had a $default value, and the user gave us input, then   #
   #  return the input; if we had a default, and they gave us no     #
   #  no input, return the $defaultValue.                            #
   #                                                                 # 
   #  if we did not have a default value, then just return whatever  #
   #  the user gave us.  if they just hit the <enter> key,           #
   #  the calling routine will have to deal with that.               #
   #-----------------------------------------------------------------#
	if ($input eq 'quit') {
		exit(0);
	}

   if ("$defaultValue") {
      return ($input ? $input : $defaultValue);    # return $_ if it has a value
   } else {
      return $input;
   }
}
